package com.zzzzzz.posts;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import com.zzzzzz.BaseDao;
import com.zzzzzz.common.page.Page;

@Repository
public class PostDao extends BaseDao {
	
	private static final String ADD = 									"insert into post(title, content, user_id, pic_id, add_time, upd_time, status) values (:title, :content, :userId, :picId, :addTime, :updTime, :status)";
	private static final String EDIT = 									"update post set title=:title, content=:content, upd_time=:updTime where id=:id";
	private static final String EDIT_STATUS = 							"update post set status = ?, upd_time = ? where id = ?";
	private static final String EDIT_PIC = 								"update post set pic_id = ?, upd_time = ? where id = ?";
	
	private static final String FIND_POSTSVIEW_BY_ID = 					"select p.id, p.title, p.content, p.pic_id, p.add_time, p.upd_time, pc.looks, pc.loves, p,user_id, u.name as user_name, u.pic_id as user_pic_id from post p inner join post_count pc on p.id = pc.post_id inner join user u on p.user_id = u.id where p.id = ?";
	
	private static final String FIND_POSTSVIEW_BY_TAGID_ORDERBY_XX =    "select p.id, p.title, p.content, p.pic_id, p.add_time, p.upd_time, pc.looks, pc.loves, p,user_id, u.name as user_name, u.pic_id as user_pic_id from post p inner join post_tag pt on p.id = pt.post_id inner join post_count pc on p.id = pc.post_id inner join user u on p.user_id = u.id where pt.tag_id = ? and pt.status = 0 and p.status = 0 order by ";
	private static final String FIND_POSTSVIEW_COUNT_BY_TAGID = 		"select count(1) as count from post p inner join post_tag pt on p.id = pt.post_id inner join post_count pc on p.id = pc.post_id inner join user u on p.user_id = u.id where pt.tag_id = ? and pt.status = 0 and p.status = 0";
	
	protected class PostRowMapper implements RowMapper<Post> {
		public Post mapRow(ResultSet rs, int rn) throws SQLException {
			Post post = new Post();
			post.setId(rs.getLong("id"));
			post.setTitle(rs.getString("title"));
			post.setContent(rs.getString("content"));
			post.setUserId(rs.getLong("user_id"));
			post.setPicId(rs.getString("pic_id"));
			post.setAddTime(rs.getTimestamp("add_time"));
			post.setUpdTime(rs.getTimestamp("upd_time"));
			post.setStatus(rs.getInt("status"));
			return post;
		}
	}
	
	protected class PostViewRowMapper implements RowMapper<PostView> {
		public PostView mapRow(ResultSet rs, int rn) throws SQLException {
			PostView postView = new PostView();
			postView.setPostId(rs.getLong("id"));
			postView.setTitle(rs.getString("title"));
			postView.setContent(rs.getString("content"));
			postView.setPicId(rs.getString("pic_id"));
			postView.setUpdTime(rs.getTimestamp("upd_time"));
			postView.setLooks(rs.getInt("looks"));
			postView.setLoves(rs.getInt("loves"));
			postView.setUserId(rs.getLong("user_id"));
			postView.setUserName(rs.getString("user_name"));
			postView.setUserPicId(rs.getString("user_pic_id"));
			return postView;
		}
	}
	
	public void add(Post post) {
		Long id = updateForLongKey(ADD, post);
		post.setId(id);
	}

	public void edit(Post post) {
		update(EDIT, post);
	}
	
	public void editStatus(Long id, Integer status) {
		update(EDIT_STATUS, status, new Date(), id);
	}
	
	public void editPic(Long id, String picId) {
		update(EDIT_PIC, picId, new Date(), id);
	}
	
	public PostView findPostViewById(Long id){
		return findOne(FIND_POSTSVIEW_BY_ID, new PostViewRowMapper(), id);
	}
	
	public Page<PostView> findPostViewByTagIdOrderBy(Integer tagId, String orderByField, Page<PostView> page){
		MapSqlParameterSource paramMap = new MapSqlParameterSource();
		paramMap.addValue("tagId", tagId);
		
		StringBuilder sql = new StringBuilder(FIND_POSTSVIEW_BY_TAGID_ORDERBY_XX);
		sql.append(orderByField);
		sql.append(limit(paramMap, page));
		
		return findPage(page,sql.toString(), FIND_POSTSVIEW_COUNT_BY_TAGID, paramMap, new PostViewRowMapper());
	}
}
